package com.gbq.qimo.mapper.impl;

import com.gbq.qimo.mapper.StudentMapper;
import com.gbq.qimo.pojo.Student;
import com.gbq.qimo.utils.JDBCUtils;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;

import java.util.ArrayList;
import java.util.List;
import java.util.Map;
import java.util.Set;

/**
 * @ClassName StudentMapperImpl
 * @Description TODO
 * @Author guobenqi
 * @Date 2021/5/29 16:00
 */
public class StudentMapperImpl implements StudentMapper {
    private JdbcTemplate template = new JdbcTemplate(JDBCUtils.getDataSource());

    @Override
    public Student findOneStudent(String stuId, String password) {
        try{
            String sql = "select * from student where username = ? and password = ? ";
            Student student = template.queryForObject(sql, new BeanPropertyRowMapper<Student>(Student.class), stuId, password);
            return student;
        }catch (Exception e){
            e.printStackTrace();
        }
        return null;
    }

    @Override
    public List<Student> findAllStudents() {
        String sql = "select  S.*,C.cName,T.tName from student S,class C,teacher T where  S.cId = C.cId and C.cTid = T.tid ";

        List<Student> studentList = template.query(sql, new BeanPropertyRowMapper<Student>(Student.class));
        return studentList;
    }

    @Override
    public int addOneStudent(Student student) {
        String sql = "insert into student values(null,?,?,?,?,?,?,?,?,?,?,?,?)";
        int update = template.update(sql, student.getUsername(), student.getcId(), student.getsCollege(), student.getsDepartment(), student.getsName(),
                student.getsSex(), student.getsAge(), student.getsAddress(), student.getsPhone(), student.getsEmail(), student.getPassword(), student.getsNation());

        return update;
    }

    @Override
    public Student findStudentById(Integer id) {
        String sql = "select  S.*,C.cName,T.tName from student S,class C,teacher T where  S.cId = C.cId and C.cTid = T.tid and id = ?";
        Student student = template.queryForObject(sql, new BeanPropertyRowMapper<>(Student.class), id);
        return student;
    }

    @Override
    public Integer updateStudent(Student student) {
        String sql = "update student set sSex=?,sAge=?,sAddress=?,sNation=? where id = ?";
        int count = template.update(sql,student.getsSex(),student.getsAge(),student.getsAddress(),student.getsNation(),student.getId());
        return count;
    }

    @Override
    public int deleteStudentById(int id) {
        String sql = "delete from student where id = ?";
        int update = template.update(sql, id);
        return update;
    }

    @Override
    public int findTotalCount(Map<String, String[]> condition) {
        String sql = "select count(*) from student where 1=1";
        StringBuilder sb = new StringBuilder(sql);
        Set<String> keySet = condition.keySet();

        List<Object> params = new ArrayList<Object>();

        for(String key:keySet){
            //排除分页条件参数
            if("currentPage".equals(key) || "rows".equals(key)){
                continue;
            }
            //获取value
            String value = condition.get(key)[0];
            //判断value是否有值
            if(value != null && !"".equals(value)){
                //有值
                sb.append(" and "+key+" like ? ");
                params.add("%"+value+"%");//？条件的值
            }
        }


        return template.queryForObject(sb.toString(),Integer.class,params.toArray());
    }

    @Override
    public List<Student> findByPage(int start, int row, Map<String, String[]> condition) {
        String sql = "select  S.*,C.cName,T.tName from student S,class C,teacher T where  S.cId = C.cId and C.cTid = T.tid ";

        StringBuilder sb = new StringBuilder(sql);
        //2.遍历map
        Set<String> keySet = condition.keySet();
        //定义参数的集合
        List<Object> params = new ArrayList<Object>();
        for (String key : keySet) {

            //排除分页条件参数
            if("currentPage".equals(key) || "rows".equals(key)){
                continue;
            }

            //获取value
            String value = condition.get(key)[0];
            //判断value是否有值
            if(value != null && !"".equals(value)){
                //有值
                if (key.equals("cId")){
                    sb.append(" and C."+key+" like ? ");
                }else{
                    sb.append(" and S."+key+" like ? ");
                }

                params.add("%"+value+"%");//？条件的值
            }
        }

        //添加分页查询
        sb.append(" limit ?,? ");
        //添加分页查询参数值
        params.add(start);
        params.add(row);
        sql = sb.toString();

        System.out.println(sql);
        return template.query(sql,new BeanPropertyRowMapper<Student>(Student.class),params.toArray());
    }

    @Override
    public Student findByUsername(String username) {
        String sql = "select * from student where username = ?";
        Student student = template.queryForObject(sql, new BeanPropertyRowMapper<Student>(Student.class), username);
        return student;
    }

    @Override
    public int register(String username,String password) {
        String sql = "update student set password = ? where username = ?";
        int count = template.update(sql,password,username);
        return count;
    }
}
